/*
 * Copyright (C) 2011 SANSOU SYSTEMS Co., Ltd. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package jp.co.sansou.android.atnd.db;

import java.util.ArrayList;
import java.util.List;

import jp.co.sansou.android.atnd.beans.AtndEventBean;
import jp.co.sansou.android.atnd.beans.AtndUsersSubBean;
import jp.co.sansou.android.atnd.common.Util;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

/**
 * データベースのデータを操作するためのクラスです。
 *
 * @author SANSOU SYSTEMS Co.
 */
public class AtndDao {

	/** 画面参照 */
	private Context context;

	private enum RW {
		READABLE, WRITABLE
	}

	/**
	 * ScheduleDaoクラスのコンストラクタです。
	 *
	 * @param db
	 *            SQLiteDatabaseオブジェクト
	 */
	public AtndDao(Context context) {
		this.context = context;
	}

	public boolean regAtndInfo(AtndEventBean... atndEventBeans) {
		boolean result = false;

		SQLiteDatabase db = getSQLiteDatabase(RW.WRITABLE);

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO MST_ATND ( event_id, title, catch, description, event_url, started_at, ended_at, url, limit, address, place, lat, lon, owner_id, owner_nickname, owner_twitter_id, accepted, waiting, updated_at ) ");
		sql.append("VALUES (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)");

		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(sql.toString());

		try {
			for (AtndEventBean bean : atndEventBeans) {

				stmt.bindLong(1, bean.getEventId());
				stmt.bindString(2, bean.getTitle());
				stmt.bindString(3, bean.getTheme());
				stmt.bindString(4, bean.getDescription());
				stmt.bindString(5, bean.getEventUrl());
				stmt.bindString(6, bean.getStartedAt());
				stmt.bindString(7, bean.getEndedAt());
				stmt.bindString(8, bean.getUrl());
				stmt.bindLong(9, bean.getLimit());
				stmt.bindString(10, bean.getAddress());
				stmt.bindString(11, bean.getPlace());
				stmt.bindDouble(12, bean.getLat());
				stmt.bindDouble(13, bean.getLon());
				stmt.bindLong(14, bean.getOwnerId());
				stmt.bindString(15, bean.getOwnerNickname());
				stmt.bindString(16, bean.getOwnerTwitterId());
				stmt.bindLong(17, bean.getAccepted());
				stmt.bindLong(18, bean.getWaiting());
				stmt.bindString(19, bean.getUpdatedAt());

				stmt.executeInsert();
			}
			db.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			stmt.close();
		}
		return result;
	}

	public boolean updAtndInfo(AtndEventBean atndEventBeans) {
		boolean result = false;

		SQLiteDatabase db = getSQLiteDatabase(RW.WRITABLE);

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE MST_ATND SET title = ? , catch = ? , description = ? , event_url = ? , started_at = ? , ended_at = ? , url = ? , limit = ? , address = ? , place = ? , lat = ? , lon = ? , owner_id = ? , owner_nickname = ? , owner_twitter_id = ? , accepted = ? , waiting = ? , updated_at = ? WHERE event_id = ? ");

		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(sql.toString());
		try {
			stmt.bindString(1, atndEventBeans.getTitle());
			stmt.bindString(2, atndEventBeans.getTheme());
			stmt.bindString(3, atndEventBeans.getDescription());
			stmt.bindString(4, atndEventBeans.getEventUrl());
			stmt.bindString(5, atndEventBeans.getStartedAt());
			stmt.bindString(6, atndEventBeans.getEndedAt());
			stmt.bindString(7, atndEventBeans.getUrl());
			stmt.bindLong(8, atndEventBeans.getLimit());
			stmt.bindString(9, atndEventBeans.getAddress());
			stmt.bindString(10, atndEventBeans.getPlace());
			stmt.bindDouble(11, atndEventBeans.getLat());
			stmt.bindDouble(12, atndEventBeans.getLon());
			stmt.bindLong(13, atndEventBeans.getOwnerId());
			stmt.bindString(14, atndEventBeans.getOwnerNickname());
			stmt.bindString(15, atndEventBeans.getOwnerTwitterId());
			stmt.bindLong(16, atndEventBeans.getAccepted());
			stmt.bindLong(17, atndEventBeans.getWaiting());
			stmt.bindString(18, atndEventBeans.getUpdatedAt());
			stmt.bindLong(19, atndEventBeans.getEventId());

			stmt.executeInsert();
			db.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			stmt.close();
		}

		return result;
	}


	public List<Integer> getEntriedEventIds(List<AtndEventBean> list) {
		SQLiteDatabase db = getSQLiteDatabase(RW.READABLE);
		List<Integer> result = new ArrayList<Integer>();

		// SQL文保持
		StringBuffer sql = new StringBuffer();

		StringBuffer selectionArgs = new StringBuffer();

		sql.append(" SELECT event_id FROM MST_ATND ");
		if (list != null && list.size() > 0) {
			sql.append("WHERE event_id in ( ");
			for (int i = 0; i < list.size(); i++) {
				AtndEventBean bean = list.get(i);
				if (i == 0) {
					sql.append(" ? ");
				} else {
					sql.append(" , ? ");
					selectionArgs.append(",");
				}
				selectionArgs.append(bean.getEventId());
			}
			sql.append(" ) ");
		}

		Cursor c = db.rawQuery(sql.toString(),
				Util.splitComma(selectionArgs.toString()));

		try {
			// 取得した値を配列に格納
			while (c.moveToNext()) {
				result.add(c.getInt(c.getColumnIndex("event_id")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeCursor(c);
		}

		return result;
	}

	public List<Integer> getEntriedUserIds(int eventId) {
		SQLiteDatabase db = getSQLiteDatabase(RW.READABLE);

		List<Integer> result = new ArrayList<Integer>();

		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT user_id FROM TRN_RECEPTION WHERE event_id = ? ORDER BY _id , user_id ");

		Cursor c = db.rawQuery(sql.toString(),
				new String[] { String.valueOf(eventId) });

		try {
			// 取得した値を配列に格納
			while (c.moveToNext()) {
				result.add(c.getInt(c.getColumnIndex("user_id")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeCursor(c);
		}

		return result;
	}


	public boolean regUserEntry(AtndUsersSubBean bean, int eventId) {
		boolean result = false;

		SQLiteDatabase db = getSQLiteDatabase(RW.WRITABLE);

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO TRN_RECEPTION ( event_id, user_id, nickname, twitter_id, twitter_img ) ");
		sql.append("VALUES (? , ? , ? , ?, ?)");

		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(sql.toString());
		try {

			stmt.bindLong(1, eventId);
			stmt.bindLong(2, bean.getUserId());
			stmt.bindString(3, bean.getNickname());
			if (bean.getTwitterId() == null) {
				stmt.bindNull(4);
			} else {
				stmt.bindString(4, bean.getTwitterId());
			}
			if (bean.getTwitterId() == null) {
				stmt.bindNull(5);
			} else {
				stmt.bindString(5, bean.getTwitterImg());
			}

			stmt.executeInsert();
			db.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			stmt.close();
		}

		return result;
	}

	public boolean delUserEntry(int eventId, int userId) {
		boolean result = false;

		SQLiteDatabase db = getSQLiteDatabase(RW.WRITABLE);

		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM TRN_RECEPTION WHERE event_id = ? AND user_id = ? ");

		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(sql.toString());
		try {
			stmt.bindLong(1, eventId);
			stmt.bindLong(2, userId);
			stmt.executeInsert();
			db.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			stmt.close();
		}

		return result;
	}

	public int getDefaultCnt(int eventId) {

		int result = 0;

		SQLiteDatabase db = getSQLiteDatabase(RW.READABLE);

		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT default_cnt FROM MST_ATND WHERE event_id = ? ");

		Cursor c = db.rawQuery(sql.toString(),
				new String[] { String.valueOf(eventId) });

		try {
			// 取得した値を配列に格納
			while (c.moveToNext()) {
				result = c.getInt(c.getColumnIndex("default_cnt"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeCursor(c);
		}
		return result;
	}

	public boolean updDefaultCnt(int eventId, int defaultCnt) {
		boolean result = false;

		SQLiteDatabase db = getSQLiteDatabase(RW.WRITABLE);

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE MST_ATND SET default_cnt = ? WHERE event_id = ? ");

		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(sql.toString());
		try {

			stmt.bindLong(1, defaultCnt);
			stmt.bindLong(2, eventId);
			stmt.executeInsert();
			db.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			stmt.close();
		}

		return result;
	}

	public List<AtndEventBean> getAtndInfo() {
		return getAtndInfo(null);
	}

	public List<AtndEventBean> getAtndInfo(List<AtndEventBean> list) {
		SQLiteDatabase db = getSQLiteDatabase(RW.READABLE);
		List<AtndEventBean> result = new ArrayList<AtndEventBean>();

		// SQL文保持
		StringBuffer sql = new StringBuffer();

		// 「?」へのバインド値
		StringBuffer selectionArgs = new StringBuffer();

		sql.append(" SELECT * FROM MST_ATND ");
		if (list != null && list.size() > 0) {
			sql.append("WHERE event_id in ( ");
			for (int i = 0; i < list.size(); i++) {
				AtndEventBean bean = list.get(i);
				if (i == 0) {
					sql.append(" ? ");
				} else {
					sql.append(" , ? ");
					selectionArgs.append(",");
				}
				selectionArgs.append(bean.getEventId());
			}
			sql.append(" ) ");
		}

		sql.append(" ORDER BY updated_at DESC ");

		Cursor c = db.rawQuery(sql.toString(),
				Util.splitComma(selectionArgs.toString()));

		try {
			// 取得した値を配列に格納
			while (c.moveToNext()) {
				AtndEventBean bean = new AtndEventBean();
				bean.setEventId(c.getInt(c.getColumnIndex("event_id")));
				bean.setTitle(c.getString(c.getColumnIndex("title")));
				bean.setOwnerId(c.getInt(c.getColumnIndex("owner_id")));
				bean.setOwnerNickname(c.getString(c
						.getColumnIndex("owner_nickname")));
				bean.setUpdatedAt(c.getString(c.getColumnIndex("updated_at")));
				result.add(bean);
			}
		} finally {
			closeCursor(c);
		}

		return result;
	}

	private SQLiteDatabase getSQLiteDatabase(RW rw) {
		SQLiteDatabase result = null;
		switch (rw) {
		case READABLE:
			result = AtndDBOpenHelper.getInstance(context)
					.getReadableDatabase();
			break;
		case WRITABLE:
			result = AtndDBOpenHelper.getInstance(context)
					.getWritableDatabase();
			break;
		}
		return result;
	}

	/**
	 * <pre>
	 * カーソルが閉じていなければ閉じる。
	 * </pre>
	 *
	 * @param c
	 *            Cursorオブジェクト
	 */
	private void closeCursor(Cursor c) {
		if (c != null && !c.isClosed()) {
			c.close();
		}
	}
}
